package com.lincoln.framework.utils;

import com.lincoln.framework.bean.ExcelBean;
import com.lincoln.framework.bean.ExplicitList;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * excel 工具类
 * 
 * @author rubekid
 * 
 */
public class ExcelUtils {

	/**
	 * 隐藏表格名称，用于默认数据存储
	 */
	private static String EXCEL_HIDE_SHEET_NAME = "excelhidesheet";

	/**
	 * 导出
	 */
	public static void export(ExcelBean excelBean) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
		HSSFRow row = sheet.createRow(0);

		// 设置样式
		HSSFCellStyle style = wb.createCellStyle();
		if (excelBean.isHeadBold()) {
			HSSFFont headfont = wb.createFont();
			headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(headfont);
		}

		HSSFCell cell;
		String[] titles = excelBean.getTitles();
		for (int i = 0; i < titles.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(new HSSFRichTextString(titles[i]));
			cell.setCellStyle(style);
			sheet.setColumnWidth(i, excelBean.getColumnWidth());
		}

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		int rowNumber = 1;
		for (String[] data : excelBean.getDataList()) {
			row = sheet.createRow(rowNumber++);
			for (int j = 0; j < data.length; j++) {
				cell = row.createCell(j);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(data[j]);
			}
		}

		Sheet hideInfoSheet = wb.createSheet(EXCEL_HIDE_SHEET_NAME);
		for (ExplicitList explicitList : excelBean.getExplicitLists()) {
			HSSFDataValidation data_validation_list = setDataValidationList(explicitList.getFirstRow(),
					explicitList.getFirstCol(), explicitList.getEndRow(), explicitList.getEndCol(),
					explicitList.getListData());
			sheet.addValidationData(data_validation_list);
			initHideExcelNameSize(wb, hideInfoSheet, sheet, explicitList);
			 wb.setSheetHidden(wb.getSheetIndex(EXCEL_HIDE_SHEET_NAME), true);
		}

		RequestAttributes attributes = RequestContextHolder.getRequestAttributes();
		HttpServletResponse response = ((ServletRequestAttributes) attributes).getResponse();
		response.setContentType("application/octet-stream;charset=utf-8");
		response.setCharacterEncoding("utf-8");

		String filename = excelBean.getName();
		filename = new String(filename.replaceAll("\\s|;", "").getBytes("gbk"), "ISO8859-1");
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-disposition", "attachment;filename=" + filename);
		OutputStream ouputStream = response.getOutputStream();
		wb.write(ouputStream);
		ouputStream.flush();
		ouputStream.close();
		wb.close();
	}

	/**
	 * 读取xls\xlsx文件
	 * 
	 * @param file
	 * @param limitColumnNumber
	 * @return
	 * @throws FileNotFoundException
	 * @throws Exception
	 *             List<ArrayList<String>>
	 */
	public static List<ArrayList<String>> read(File file, String fileName, int limitColumnNumber)
			throws FileNotFoundException, Exception {
		if (fileName.endsWith(".xls")) {
			return readXls(new FileInputStream(file), limitColumnNumber);
		} else if (fileName.endsWith(".xlsx")) {
			return readXlsx(new FileInputStream(file), limitColumnNumber);
		}
		return null;
	}

	/**
	 * xlsx文档
	 */
	private static List<ArrayList<String>> readXlsx(InputStream inputstream, int limitColumnNumber) throws Exception {
		List<ArrayList<String>> arrayLists = new ArrayList<ArrayList<String>>();
		XSSFWorkbook hssfworkbook = new XSSFWorkbook(inputstream);
		XSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
		if (hssfsheet != null) {
			int totalrows = hssfsheet.getPhysicalNumberOfRows();// --获取sheet总行数
			if (totalrows > 1) {
				for (int i = 0; i < totalrows; i++) {
					XSSFRow hssfrow = hssfsheet.getRow(i);
					int cellNumbers = hssfrow.getPhysicalNumberOfCells();
					List<String> columnValues = new ArrayList<String>();
					for (int j = 0; j < limitColumnNumber; j++) {
						if (j + 1 <= cellNumbers) {
							Cell cell = hssfrow.getCell(j);
							if (cell != null) {
								cell.setCellType(Cell.CELL_TYPE_STRING);
								columnValues.add(cell.getStringCellValue());
								continue;
							}
						}
						columnValues.add("");
					}

					arrayLists.add((ArrayList<String>) columnValues);
				}
			}
		}
		hssfworkbook.close();
		return arrayLists;
	}

	/**
	 * xls文档
	 */
	public static List<ArrayList<String>> readXls(InputStream inputstream, int limitColumnNumber) throws Exception {
		List<ArrayList<String>> arrayLists = new ArrayList<ArrayList<String>>();
		HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
		if (hssfsheet != null) {
			int totalrows = hssfsheet.getPhysicalNumberOfRows();// --获取sheet总行数
			if (totalrows > 1) {
				for (int i = 0; i < totalrows; i++) {
					HSSFRow hssfrow = hssfsheet.getRow(i);
					int cellNumbers = hssfrow.getPhysicalNumberOfCells();
					List<String> columnValues = new ArrayList<String>();
					for (int j = 0; j < limitColumnNumber; j++) {
						if (j + 1 <= cellNumbers) {
							Cell cell = hssfrow.getCell(j);
							if (cell != null) {
								cell.setCellType(Cell.CELL_TYPE_STRING);
								columnValues.add(cell.getStringCellValue());
								continue;
							}
						}
						columnValues.add("");
					}

					arrayLists.add((ArrayList<String>) columnValues);
				}
			}
		}
		hssfworkbook.close();
		return arrayLists;
	}

	/**
	 * 设置单元默认值
	 */
	private static HSSFDataValidation getDataValidationByFormula(String formulaString, short firstRow, short firstCol,
			short endRow, short endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
		// 设置数据有效性加载在哪个单元格上。

		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);

		return data_validation_list;
	}

	/**
	 * 设置单元默认值
	 * 
	 * @param firstRow
	 * @param firstCol
	 * @param endRow
	 * @param endCol
	 * @param dataList
	 * @return HSSFDataValidation
	 */
	private static HSSFDataValidation setDataValidationList(short firstRow, short firstCol, short endRow, short endCol,
			String[] dataList) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(dataList);
		// 设置数据有效性加载在哪个单元格上。

		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);

		return data_validation_list;
	}

	/**
	 * 创建一个名称
	 * 
	 * @param workbook
	 */
	private static void initHideDataSize(Workbook workbook, String nameCode, int columnNumber, int rowNumber,
			int size) {
		Name name = workbook.createName();
		name.setNameName(nameCode);
		name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME + "!" + getExcelNameListSize(columnNumber, rowNumber, size));
	}

	/**
	 * 创建一行数据
	 * 
	 * @param currentRow
	 * @param textList
	 */
	private static void createRow(Row currentRow, String[] textList) {
		if (textList != null && textList.length > 0) {
			int i = 0;
			for (String cellValue : textList) {
				Cell userNameLableCell = currentRow.createCell(i++);
				userNameLableCell.setCellValue(cellValue);
			}
		}
	}

	/**
	 * 创建名称表格中引用位置
	 * 
	 * @param workbook
	 */
	private static String getExcelNameListSize(int columnNumber, int rowNumber, int size) {
		String start = getColumnNumber(columnNumber);
		String end = getColumnNumber(columnNumber + size - 1);
		return "$" + start + "$" + rowNumber + ":$" + end + "$" + rowNumber;
	}

	/**
	 * 列数转换
	 */
	private static String getColumnNumber(int column) {
		String strResult = "";
		int intRound = column / 26;
		int intMod = column % 26;
		if (intRound != 0) {
			strResult = String.valueOf(((char) (intRound + 64)));
		}
		strResult += String.valueOf(((char) (intMod + 64)));
		return strResult;
	}

	/**
	 * 初始化隐藏表格中数据，并设置当前单元格序列
	 */
	private static void initHideExcelNameSize(HSSFWorkbook wb, Sheet hideInfoSheet, Sheet operateSheet,
			ExplicitList explicitList) {
		if (explicitList.getList() != null && explicitList.getList().size() > 0) {
			for (int i = 0; i < explicitList.getListData().length; i++) {
				String name = explicitList.getListData()[i];
				ExplicitList data = explicitList.getList().get(i);
				if(data.getListData().length > 0) {
					int rowNumber = hideInfoSheet.getPhysicalNumberOfRows() + 1;
					// 创建一行，用于插入数据
					Row row = hideInfoSheet.createRow(rowNumber - 1);
					createRow(row, data.getListData());
					// 设置名称数据范围，初始化一条数据
					initHideDataSize(wb, name, 1, rowNumber, data.getListData().length);
				}
				// 设置序列来源
				DataValidation dataValidationList = getDataValidationByFormula(
						"indirect("
								+ getExcelNameListSize(explicitList.getFirstCol() + 1, explicitList.getEndRow() + 1,
										1) + ")", data.getFirstRow(), data.getFirstCol(), data.getEndRow(),
										data.getEndCol());
				operateSheet.addValidationData(dataValidationList);
				if (data != null) {
					initHideExcelNameSize(wb, hideInfoSheet, operateSheet, data);
				}
			}
		}
	}
}
